package com.clei.utils;

import com.clei.dto.db.ColumnProperty;
import com.clei.dto.db.MySQLExportConfig;
import com.clei.dto.db.TableInfo;
import com.clei.enums.DatabaseServerEnum;
import com.clei.utils.helper.DBHelper;
import com.clei.utils.helper.TimeRecorder;

import java.io.File;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * MySQL 数据导出 util
 *
 * @author Y
 * @date 2024-01-09
 **/
public class MySQLExportUtil {

    /**
     * db
     */
    private final static DatabaseServerEnum DB_SERVER = DatabaseServerEnum.MYSQL;

    /**
     * deHelper
     */
    private final static DBHelper DB_HELPER = DB_SERVER.getDbHelper();

    /**
     * 查询表sql
     * SHOW TABLES;亦可
     */
    private final static String SELECT_TABLE_SQL
            = "SELECT TABLE_NAME FROM `information_schema`.`TABLES` WHERE TABLE_SCHEMA = '%s' ORDER BY TABLE_NAME ASC;";

    /**
     * 查询数据sql
     */
    private final static String SELECT_DATA_SQL = "SELECT * FROM `%s`;";

    /**
     * 创建表sql
     */
    private final static String CREATE_TABLE_SQL = "SHOW CREATE TABLE `%s`.`%s`;";

    /**
     * 删除表sql
     */
    private final static String DROP_TABLE_SQL = "DROP TABLE IF EXISTS `%s`;";

    /**
     * TimeRecorder
     */
    private static final TimeRecorder RECORDER = new TimeRecorder();

    public static void main(String[] args) {
        MySQLExportConfig exportConfig = new MySQLExportConfig();
        ReadUtil.toEntity(MySQLExportConfig.getPropertyList(), exportConfig);
        if (!exportConfig.assemble()) {
            return;
        }
        MybatisUtil.execMultipleOperation(DB_SERVER, () -> export(exportConfig));
        RECORDER.printAll();
    }

    /**
     * 导出
     *
     * @param exportConfig 导出配置
     */
    public static void export(MySQLExportConfig exportConfig) {
        Map<String, TableInfo> tableMap = getTableMap(exportConfig);
        RECORDER.record("表信息获取");
        if (CollectionUtil.isEmpty(tableMap)) {
            PrintUtil.log("未查询到表");
            return;
        }
        long timestamp = System.currentTimeMillis();
        Integer exportType = exportConfig.getExportTypeValue();
        String exportPath = exportConfig.getExportPath();
        // 结构
        if (DB_HELPER.exportStructure(exportType)) {
            List<String> lineList = new ArrayList<>(4 + tableMap.size() * 2);
            lineList.add("-- " + DateUtil.currentDateTime(true));
            lineList.add("-- table structure count : " + tableMap.size());
            lineList.add(getSetForeignKeyCheck(false));
            tableMap.forEach((k, v) -> {
                lineList.add(String.format(DROP_TABLE_SQL, k));
                lineList.add(v.getCreateSql());
            });
            lineList.add(getSetForeignKeyCheck(true));
            FileUtil.writeTxt(exportPath + File.separator + DB_SERVER + "_structure_" + timestamp + ".sql", lineList);
            RECORDER.record("导出结构");
        }
        // 数据
        if (!DB_HELPER.exportData(exportType)) {
            return;
        }
        String filePath = exportPath + File.separator + DB_SERVER + "_data_" + timestamp + ".sql";
        int insertLimit = 500;
        FileUtil.writeTxt(filePath, Collections.singletonList(getSetForeignKeyCheck(false)), true);
        tableMap.forEach((k, v) -> {
            String valueTemplate = v.getInsertValues();
            List<Object[]> tableData = getTableData(v);
            List<List<Object[]>> lists = CollectionUtil.cut(tableData, insertLimit);
            // 写入
            for (List<Object[]> dataList : lists) {
                List<String> lineList = new ArrayList<>(1 + dataList.size());
                lineList.add("-- data " + k + " count : " + dataList.size());
                lineList.add(String.format(v.getInsertPrefix(), k));
                int size = dataList.size();
                int forLimit = size - 1;
                // 前n行
                for (int i = 0; i < forLimit; i++) {
                    String value = String.format(valueTemplate, dataList.get(i)) + ",";
                    lineList.add(value);
                }
                // 最后一行
                if (size > 0) {
                    String value = String.format(valueTemplate, dataList.get(forLimit)) + ";\n";
                    lineList.add(value);
                }
                // append
                FileUtil.writeTxt(filePath, lineList, true);
            }
        });
        FileUtil.writeTxt(filePath, Collections.singletonList(getSetForeignKeyCheck(true)), true);
        RECORDER.record("导出数据");
    }

    /**
     * 获取表信息
     *
     * @param exportConfig 导出配置
     */
    private static Map<String, TableInfo> getTableMap(MySQLExportConfig exportConfig) {
        String database = exportConfig.getDatabase();
        // 表筛选
        String selectSql = String.format(SELECT_TABLE_SQL, database);
        List<String> tableNameList = MybatisUtil.commonMultipleOperation(mapper -> mapper.selectString(selectSql));
        if (!CollectionUtil.isEmpty(exportConfig.getTableList()) && !CollectionUtil.isEmpty(tableNameList)) {
            Set<String> tableSet = new HashSet<>(tableNameList);
            tableNameList = exportConfig.getTableList().stream().filter(tableSet::contains).collect(Collectors.toList());
        }
        // 排除
        tableNameList = tableNameList.stream().filter(t -> !exportConfig.getExcludeTableSet().contains(t)).collect(Collectors.toList());
        // 使用LinkedHashMap保证顺序
        Map<String, TableInfo> resMap = new LinkedHashMap<>(tableNameList.size());
        // 表创建语句 列属性数据
        Integer exportType = exportConfig.getExportTypeValue();
        boolean exportStructure = DB_HELPER.exportStructure(exportType);
        boolean exportData = DB_HELPER.exportData(exportType);
        for (String table : tableNameList) {
            String tableCreateSql = exportStructure ? getTableCreateSql(database, table) : null;
            List<ColumnProperty> columnList = exportData ? getColumnList(database, table) : Collections.emptyList();
            resMap.put(table, new TableInfo(DB_SERVER, table, tableCreateSql, columnList));
        }
        return resMap;
    }

    /**
     * 获取表数据
     *
     * @param tableInfo 表信息
     */
    private static List<Object[]> getTableData(TableInfo tableInfo) {
        String dataSql = String.format(SELECT_DATA_SQL, tableInfo.getTableName());
        List<Map<String, Object>> dataList = MybatisUtil.commonMultipleOperation(mapper -> mapper.selectMap(dataSql));
        List<ColumnProperty> columnList = tableInfo.getColumnList();
        int columnSize = columnList.size();
        return dataList.stream()
                .map(m -> {
                    Object[] data = new Object[columnSize];
                    int i = 0;
                    for (ColumnProperty column : columnList) {
                        Object obj = m.get(column.getColumnName());
                        data[i++] = DB_HELPER.getStringForInsert(obj);
                    }
                    return data;
                }).collect(Collectors.toList());
    }

    /**
     * 获取表创建sql
     *
     * @param database  数据库
     * @param tableName 表名
     * @return map
     */
    private static String getTableCreateSql(String database, String tableName) {
        String showCreateSql = String.format(CREATE_TABLE_SQL, database, tableName);
        List<Map<String, Object>> createSqlMap = MybatisUtil.commonMultipleOperation(mapper -> mapper.selectMap(showCreateSql));
        String tableSql = null;
        if (CollectionUtil.isEmpty(createSqlMap)) {
            PrintUtil.log("获取表创建SQL失败 tableName : {}", tableName);
        } else {
            for (Map<String, Object> s : createSqlMap) {
                Object createObj = s.get("Create Table");
                tableSql = String.valueOf(createObj);
                tableSql = updateCharsetAndIncr(tableSql) + ";";
            }
        }
        return tableSql;
    }

    /**
     * 字符集和自增处理
     * 将字符集修改为utf8bmb4 排序规则修改为utf8mb4_general_ci 自增值去掉
     *
     * @param createTableSql 建表sql
     * @return 建表sql
     */
    private static String updateCharsetAndIncr(String createTableSql) {
        String sql = createTableSql.replaceAll("CHARACTER SET [a-zA-z0-9]*", "");
        sql = sql.replaceAll("COLLATE [_a-zA-z0-9]*", "");
        sql = sql.replaceAll("CHARSET=[a-zA-z0-9]*", "CHARSET=utf8mb4");
        sql = sql.replaceAll("COLLATE=[_a-zA-z0-9]*", "COLLATE=utf8mb4_general_ci");
        sql = sql.replaceAll("AUTO_INCREMENT=\\d*", " ");
        return sql;
    }

    /**
     * getColumnList
     *
     * @param database  数据库
     * @param tableName 表名
     * @return 列
     */
    private static List<ColumnProperty> getColumnList(String database, String tableName) {
        List<Map<String, String>> columnList = MybatisUtil.commonMultipleOperation(mapper -> mapper.selectMysqlColumn(database, tableName));
        return ColumnProperty.columnPropertyList(columnList, DB_HELPER::getJavaType);
    }

    /**
     * 外键检查 set语句
     *
     * @param check true/false
     * @return 外键检查 set语句
     */
    private static String getSetForeignKeyCheck(boolean check) {
        return "SET FOREIGN_KEY_CHECKS = " + (check ? 1 : 0) + ";\n";
    }
}
